/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.CarDetailDTO;
import east2west.interfaces.CarDetailInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author anhtuan
 */
public class CarDetailDAO implements CarDetailInterface {

    Connection con = null;
    CarDetailDTO cardetai = null;
    CallableStatement call = null;
    Statement st = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    private final String SQL_READ_BY_ALL = "select * from V_CarDetail";
    private final String SQL_READ_BY_ID = "select * from V_CarDetail where CarDetailId = ?";
    private final String SQL_READ_BY_CARID = "select * from V_CarDetail where CarId = ?";
    private final String SQL_CREATE = "{call sp_Insert_CarDetail (?,?)}";
    private final String SQL_UPDATE = "{call sp_Update_CarDetail (?,?,?)}";
    private final String SQL_DELETE = "{call sp_Delete_CarDetail (?)}";
    private final String SQL_DELETE_CARID = "{call sp_Delete_CarDetail_CarId (?)}";

    public ArrayList<CarDetailDTO> readByAll() {
        ArrayList<CarDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            st = con.createStatement();

            rs = st.executeQuery(SQL_READ_BY_ALL);
            list = new ArrayList<CarDetailDTO>();
            while (rs.next()) {
                cardetai = new CarDetailDTO();
                cardetai.setCarDetailId(rs.getInt(1));
                cardetai.setCarId(rs.getInt(2));
                cardetai.setImageUrl(rs.getString(3));
                list.add(cardetai);
            }
            rs.close();
            st.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public CarDetailDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_ID);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            while (rs.next()) {
                cardetai = new CarDetailDTO();
                cardetai.setCarDetailId(rs.getInt(1));
                cardetai.setCarId(rs.getInt(2));
                cardetai.setImageUrl(rs.getString(3));
            }
            rs.close();
            pst.close();
            return cardetai;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarDetailDTO> findByCarId(int id) {
        ArrayList<CarDetailDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement(SQL_READ_BY_CARID);
            pst.setInt(1, id);
            rs = pst.executeQuery();
            list = new ArrayList<CarDetailDTO>();
            while (rs.next()) {
                cardetai = new CarDetailDTO();
                cardetai.setCarDetailId(rs.getInt(1));
                cardetai.setCarId(rs.getInt(2));
                cardetai.setImageUrl(rs.getString(3));
                list.add(cardetai);
            }
            rs.close();
            pst.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }
    // PreparedStatement pst = null;

    public CarDetailDTO getTop1ImageFromCarId(int id) {

        try {
            con = DatabaseUtil.getConnection();
            pst = con.prepareStatement("select top 1 * from V_CarDetail where CarId = ?");
            pst.setInt(1, id);
            rs = pst.executeQuery();

            while (rs.next()) {
                cardetai = new CarDetailDTO();
                cardetai.setCarDetailId(rs.getInt(1));
                cardetai.setCarId(rs.getInt(2));
                cardetai.setImageUrl(rs.getString(3));
            }
            rs.close();
            pst.close();
            return cardetai;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public boolean create(CarDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_CREATE);
            call.setInt(1, dto.getCarId());
            call.setString(2, dto.getImageUrl());
            call.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean update(CarDetailDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_UPDATE);
            call.setInt(1, dto.getCarDetailId());
            call.setInt(2, dto.getCarId());
            call.setString(3, dto.getImageUrl());
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_DELETE);
            call.setInt(1, id);
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean deleteCarId(int carId) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_DELETE_CARID);
            call.setInt(1, carId);
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean delete(CarDetailDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
